/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.suli.inventory.repository;

import me.zhengjie.suli.inventory.domain.Inventory;
import me.zhengjie.suli.outHouse.domain.Orders;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

/**
* @website https://eladmin.vip
* @author WYH
* @date 2024-02-01
**/
public interface InventoryRepository extends JpaRepository<Inventory, String>, JpaSpecificationExecutor<Inventory> {

    @Query(value = "SELECT * FROM inventory WHERE material_code = ?1 AND is_hold = 0" ,nativeQuery = true)
    public List<Inventory> findByMaterialCode(String materialCode);

    @Query(value = "SELECT * FROM inventory where traycode like '%B%'" ,nativeQuery = true)
    public List<Inventory> findByCCC();

    @Query(value = "SELECT * FROM inventory WHERE lot_code = ?1 AND is_hold = 0" ,nativeQuery = true)
    public List<Inventory> findByLotCode(String lotCode);

    @Query(value = "SELECT top(1) * FROM inventory WHERE box_number = ?1  order by start_date DESC " ,nativeQuery = true)
    public Inventory findByBoxNumber(String boxNumber);

    @Query(value = "SELECT top(1) * FROM inventory WHERE box_number = ?1  AND available_quantity = 0 order by start_date DESC " ,nativeQuery = true)
    public Inventory findByBoxNumberAndCount(String boxNumber);

    @Query(value = "SELECT  * FROM inventory WHERE is_hold = 0 AND warehouse_location != '暂存区' order by material_code " ,nativeQuery = true)
    public List<Inventory> findByNotHold();

    @Query(value = "SELECT  * FROM inventory WHERE id in (?1) AND warehouse_location != '暂存区' order by material_code " ,nativeQuery = true)
    public List<Inventory> findByIdList(List<String> ids);
    @Query(value = "SELECT  * FROM inventory WHERE box_number in (?1)  " ,nativeQuery = true)
    public List<Inventory> findByBoxNumbers(List<String> boxNumber);

    @Query(value = "SELECT  * FROM inventory WHERE update_time IS NULL OR update_time >= DATEADD( SECOND, -30, GETDATE( ) ) " ,nativeQuery = true)
    public List<Inventory> findAllByUpdateTime();

    @Query(value = "SELECT top(1) * FROM inventory WHERE box_number = ?1 AND warehouse_location = '暂存区' order by start_date DESC " ,nativeQuery = true)
    public Inventory findByBoxNumberAndLocation(String boxNumber);

    @Query(value = "SELECT top(1) * FROM inventory WHERE box_number = ?1 AND warehouse_location != '暂存区' order by start_date DESC " ,nativeQuery = true)
    public Inventory findByBoxNumberAndNotLocation(String boxNumber);

    @Query(value = "SELECT * FROM inventory WHERE lot_code = ?1 AND material_code = ?2" ,nativeQuery = true)
    public List<Inventory> findByLotCodeAndElNo(String lotCode, String elNo);

    @Query(value = "SELECT * FROM inventory WHERE lot_code = ?1 AND grade = 'F'" ,nativeQuery = true)
    public List<Inventory> findByLotCodeAndGrade(String lotNo);

    @Query(value = "SELECT * FROM inventory WHERE lot_code in (?1)" ,nativeQuery = true)
    public List<Inventory> findByLotCodes(List<String> materialCode);

    @Query(value = "SELECT sum(available_quantity) FROM inventory WHERE material_code = ?1 AND is_hold = 0" ,nativeQuery = true)
    public BigDecimal findCountByMaterialCode(String materialCode);

    @Query(value = "SELECT * FROM inventory WHERE material_code in (?1) AND (su_no != null or su_no != '')" ,nativeQuery = true)
    public List<Inventory> findByMaterialCodes(List<String> materialCode);

    @Query(value = "select top(1) * from inventory where material_code = ?1 and box_number = ?2 order by start_date DESC ", nativeQuery = true)
    Inventory getInventoryByMaterialCodeAndBoxNumber(String materialCode, String boxNumber);

    /** 根据物料编码查找在4层及一下有没有库存 */
    @Query(value = "select inventory.* from inventory, location where inventory.warehouse_location = location.location_code and location.layer <= 4 and material_code = ?1 and warehouse_location !='暂存区'", nativeQuery = true)
    List<Inventory> getInventoryByMaterialCodeWithLayerLessThen4(String materialCode);

    @Query(value = "SELECT * FROM inventory WHERE traycode = ?1 " ,nativeQuery = true)
    public List<Inventory> findByTrayCode(String trayCode);


    @Query(value = "SELECT top(1) * FROM inventory WHERE traycode = ?1 " ,nativeQuery = true)
    public Inventory findByTrayCodeTop(String trayCode);

    @Query(value = "SELECT top(1) * FROM inventory WHERE traycode = ?1 AND box_number = ?2" ,nativeQuery = true)
    public Inventory findByTrayCodeAndBoxNumberTop(String trayCode,String boxNumber);

    @Query(value = "SELECT * FROM inventory WHERE lot_code = ?1 AND material_code = ?2 AND available_quantity != 0  AND is_hold = 0 AND grade != 'F' AND zone_code = ?3 order by create_time desc" ,nativeQuery = true)
    public List<Inventory> findByLotNoAndElNo(String lotNo,String elNo,String waNo);

    @Query(value = "SELECT * FROM inventory WHERE lot_code = ?1 AND material_code = ?2  AND is_hold = 2 AND grade = 'F' AND zone_code = ?3 order by create_time desc" ,nativeQuery = true)
    public List<Inventory> thawSelect(String lotNo,String elNo,String waNo);

    @Query(value = "SELECT * FROM inventory WHERE lot_code = ?1 AND material_code = ?2 AND hold_quantity != 0  AND is_hold != 0 AND grade = 'F'" ,nativeQuery = true)
    public List<Inventory> findByLotNoAndElNoAndVmiNot(String lotNo,String elNo);

    @Query(value = "SELECT * FROM inventory WHERE lot_code = ?1 AND material_code = ?2 AND available_quantity != 0 AND is_hold != 1" ,nativeQuery = true)
    public List<Inventory> findByLotNoAndElNoAndIsHold(String lotNo,String elNo);
    @Query(value = "SELECT * FROM inventory WHERE  material_code = ?1 AND is_hold = 0 AND available_quantity != 0 AND vmi_code = 'Y' AND zone_code = ?2 AND su_no = ?3 order by create_time desc" ,nativeQuery = true)
    public List<Inventory> findByLotNoAndElNoAndNotVmi(String elNo,String waNo,String suNo);

    @Query(value = "SELECT * FROM inventory WHERE material_code = ?1 AND is_hold = 0 AND available_quantity != 0 AND vmi_code != 'Y' AND zone_code = ?2 order by create_time desc" ,nativeQuery = true)
    public List<Inventory> findByLotNoAndElNoAndVmi(String elNo,String waNo);

    @Query(value = "SELECT * FROM inventory WHERE lot_code = ?1 AND material_code = ?2 AND available_quantity != 0 AND is_hold = 0 AND vmi_code != 'Y' AND zone_code = ?3" ,nativeQuery = true)
    public List<Inventory> findByLotNoAndElNoAndVmiAAndNotIsHold(String lotNo,String elNo,String waNo);

    @Query(value = "SELECT * FROM inventory WHERE locationCode = ?1 AND material_code = ?2" ,nativeQuery = true)
    public List<Inventory> findByLocationCodeAndMaterialCode(String locationCode, String materialCode);

    @Query(value = "SELECT * FROM inventory WHERE material_code in (?1) AND is_hold != 2 AND available_quantity != 0 AND warehouse_location IS NOT NULL AND warehouse_location != ''  ORDER BY  vmi_code ASC ,el_mkdate DESC" ,nativeQuery = true)
    public List<Inventory> findByMaterialCodesAndIsHold(List<String> materialCode);

    @Query(value = "SELECT * FROM inventory WHERE material_code in (?1) AND is_hold = 0 AND available_quantity != 0 AND vmi_code != 'Y' ORDER BY el_mkdate DESC" ,nativeQuery = true)
    public List<Inventory> findByMaterialCodesAndIsHoldAndNotVmi(List<String> materialCode);

    @Query(value = "SELECT * FROM inventory WHERE lot_code = ?1 AND material_code = ?2 AND available_quantity != 0 AND is_hold = 0 AND vmi_code != 'Y' AND wa_no = ?3" ,nativeQuery = true)
    public List<Inventory> findByLotNoAndElNoAndVmiAAndNotIsHoldAndWaNo(String lotNo,String elNo,String waNo);

    /** 库存1级子表, 根据 批次号计算总数 */
    @Query(value = "select lot_code, sum(available_quantity) as available_quantity, sum(hold_quantity) as hold_quantity, sum(total) as total from inventory where material_code = ?1 group by lot_code", nativeQuery = true)
    List<Map<String, Object>> findInventoriesByMaterialCode(String materialCode);

    /** 库存2级子表 */
    @Query(value = "select * from inventory where material_code = ?1 and lot_code = ?2", nativeQuery = true)
    List<Inventory> findInventoriesByMaterialCodeAndLotCode(String materialCode, String lotCode);

    /** 首页库存表格，改柱状统计图 */
    @Query(value = "select sum(available_quantity) as qty, material_code, material_name from inventory where material_code <> '20232201043T3G6' GROUP BY material_code, material_name ORDER BY material_code OFFSET (?1 * 5) ROWS FETCH NEXT 5 ROWS ONLY", nativeQuery = true)
    List<Map<String, String>> getInventoriesByMaterialCodeGroupWithNoTrayCode(int pageNum);

    /** 库存主表，按物料分类 */
    @Query(value = "select sum(available_quantity) as available_quantity, SUM ( total ) AS total, SUM ( hold_quantity ) AS hold_quantity, material_code, material_name from inventory where ?3 GROUP BY material_code, material_name ORDER BY " +
            "material_code OFFSET (?1 * ?2) ROWS FETCH NEXT ?2 ROWS ONLY ", nativeQuery = true)
    List<Map<String, Object>> getInventoryGroupByMaterial(int pageNum, int listNumber, Object whereCondition);


    /** 库存主表，计算分组后的总数 */
    @Query(value = "select sum(available_quantity) as qty, material_code, material_name from inventory GROUP BY material_code, material_name ORDER BY material_code", nativeQuery = true)
    List<Map<String, Object>> getCount();

    /** 自动理货， 筛选需要自动理货的物料编码（常温库） */
    @Query(value = "select ISNULL(num, 0) as num , tableC.material_code_1, tableC.material_name_1 from " +
            "( select * from (select COUNT(inventory.id) as num, material_code, material_name from inventory, location where inventory.location_code = location.location_code and location.layer <= 4 GROUP BY material_code, material_name) as tableA " +
            "right join " +
            "(select DISTINCT material_code as material_code_1, material_name as material_name_1 from inventory) as tableB ON tableA.material_code = tableB.material_code_1 ) as tableC " +
            "where material_name_1 <> '过载保护器' and ISNULL(num, 0) < 2 " +
            "ORDER BY material_code_1 asc", nativeQuery = true)
    List<Map<String, Object>> autoTallyInventorySelect();

    /** 自动理货，筛选需要自动理货的恒温库（过载保护器） */
    @Query(value = "select ISNULL(num, 0) as num , tableC.material_code_1, tableC.material_name_1 from " +
            "( select * from (select COUNT(inventory.id) as num, material_code, material_name from inventory, location where inventory.location_code = location.location_code and location.layer <= 4 GROUP BY material_code, material_name) as tableA " +
            "right join " +
            "(select DISTINCT material_code as material_code_1, material_name as material_name_1 from inventory) as tableB ON tableA.material_code = tableB.material_code_1 ) as tableC " +
            "where material_name_1 = '过载保护器' and ISNULL(num, 0) < 1 " +
            "ORDER BY material_code_1 asc", nativeQuery = true)
    List<Map<String, Object>> autoTallyInventorySelectHW();

    /** 自动理货，按物料编码查找，库存位置大于4， 制造时间正序 */
    @Query(value = "select top(?2) inventory.* from inventory, location where inventory.location_code = location.location_code and location.layer > 4 and material_code = ?1 order by el_mkdate asc", nativeQuery = true)
    List<Inventory> getInventoriesByMaterialCodeOrderByElMkDateAscWithLayerGreaterThen4(String materialCOde, int qty);

    /** 自动理货，筛选哪个物料在4层及一下的库存箱数最多 */
    @Query(value = "select top(1) count(inventory.id) as num, material_code from inventory, location where inventory.location_code = location.location_code and layer <= 4 " +
            "GROUP BY material_code ORDER BY num desc;", nativeQuery = true)
    Inventory getInventoryByMaterialCodeWithMostQtyLessThenLayer4();

    @Query(value = "SELECT * FROM inventory WHERE material_code = ?1 AND is_hold != 2 " ,nativeQuery = true)
    public List<Inventory> findByMaterialCodeAndCount(String materialCode);

}